package com.example.demo01.Impl.webImpl;

import com.example.demo01.Dao.webDao.NameValueDao;
import com.example.demo01.Entity.webEntity.NameValue;
import static com.example.demo01.utils.DateParseUtils.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class NameValueDaoImpl implements NameValueDao {

    //自动注入
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取每一个护理等级对应的人数
     * */
    @Override
    public List<NameValue> staticByNurseLevel() {
        String sqlQuery="SELECT userinfo1.nurselevel as name,COUNT(*) as value FROM userinfo1 GROUP BY userinfo1.nurselevel ORDER BY userinfo1.nurselevel";
        return jdbcTemplate.query(sqlQuery, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1).replace("\u00A0", "").trim(),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 按照省份代码统计人数
     * */
    @Override
    public List<NameValue> staticByProvinceCode() {
        String sqlQuery="SELECT DISTINCT chinaencode.domain as name,COUNT(userinfo1.idnum)as value FROM userinfo1,chinaencode WHERE LEFT(userinfo1.idnum,2)=LEFT(chinaencode.cvalue,2) AND RIGHT(chinaencode.cvalue,4)='0000' GROUP BY LEFT(userinfo1.idnum,2) ORDER BY value ASC";
        return jdbcTemplate.query(sqlQuery, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 按照城市代码统计人数
     * */
    @Override
    public List<NameValue> staticByCityCode() {
        String sqlQuery="SELECT DISTINCT chinaencode.domain as name,COUNT(userinfo1.idnum) as value FROM userinfo1,chinaencode WHERE LEFT(userinfo1.idnum,4)=LEFT(chinaencode.cvalue,4) AND RIGHT(chinaencode.cvalue,2)='00' GROUP BY LEFT(userinfo1.idnum,4) ORDER BY value ASC";
        return jdbcTemplate.query(sqlQuery, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 按照县区代码统计人数
     * */
    @Override
    public List<NameValue> staticByCountryCode() {
        String sqlQuery="SELECT DISTINCT chinaencode.domain as name,COUNT(userinfo1.idnum) as value FROM userinfo1,chinaencode WHERE LEFT(userinfo1.idnum,6)=chinaencode.cvalue GROUP BY LEFT(userinfo1.idnum,6) ORDER BY value ASC";
        return jdbcTemplate.query(sqlQuery, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 获取每一个护理等级对应的历史报警次数
     * */
    @Override
    public List<NameValue> staticByNurseLevelforRose() {
        String sqlQuery="SELECT userinfo1.nurselevel as name,COUNT(*)as value FROM userinfo1,warnrecord WHERE warnrecord.idnumber=userinfo1.idnum GROUP BY userinfo1.nurselevel";
        return jdbcTemplate.query(sqlQuery, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 按照省份代码统计历史报警次数
     * */
    @Override
    public List<NameValue> staticByProvinceCodeforRose() {
        String sql="SELECT chinaencode.domain as name, COUNT(*) as value FROM chinaencode,warnrecord WHERE LEFT(chinaencode.cvalue,2)=LEFT(warnrecord.idnumber,2) AND RIGHT(chinaencode.cvalue,4)='0000' GROUP BY name";
        return jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 按照城市代码统计历史报警次数
     * */
    @Override
    public List<NameValue> staticByCityCodeforRose() {
        String sql="SELECT chinaencode.domain as name, COUNT(*) as value FROM chinaencode,warnrecord WHERE LEFT(chinaencode.cvalue,4)=LEFT(warnrecord.idnumber,4) AND RIGHT(chinaencode.cvalue,2)='00' GROUP BY name";
        return jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 按照县区代码统计历史报警次数
     * */
    @Override
    public List<NameValue> staticByCountryCodeforRose() {
        String sql="SELECT chinaencode.domain as name, COUNT(*) as value FROM chinaencode,warnrecord WHERE LEFT(chinaencode.cvalue,6)=LEFT(warnrecord.idnumber,6) GROUP BY name";
        return jdbcTemplate.query(sql, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 获取每一个护理等级在特定时间区间内的报警次数
     * */
    @Override
    public List<NameValue> staticByNurseLevelforRose(Long startTime, Long endTime) {
        String sql="SELECT userinfo1.nurselevel as name,COUNT(*) as value FROM (SELECT * FROM signedrecord WHERE signedrecord.signeddate BETWEEN ? AND ?) A,userinfo1 WHERE userinfo1.idnum=A.objidcard GROUP BY userinfo1.nurselevel";
        String startDateTime = getDateStrByFormat(millionsToUtilDate(startTime), "yyyy-MM-dd HH:mm:ss");
        String endDateTime = getDateStrByFormat(millionsToUtilDate(endTime), "yyyy-MM-dd HH:mm:ss");
        return jdbcTemplate.query(sql, new Object[]{startDateTime, endDateTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 按照省份和起止时间统计报警次数
     * @param startTime 起始时间
     * @param endTime 结束时间
     * */
    @Override
    public List<NameValue> staticByProvinceCodeforRose(Long startTime, Long endTime) {
        String sql="SELECT chinaencode.domain as name, COUNT(*) as value FROM chinaencode,warnrecord WHERE LEFT(chinaencode.cvalue,2)=LEFT(warnrecord.idnumber,2) AND RIGHT(chinaencode.cvalue,4)='0000' AND warnrecord.warntime BETWEEN ? AND ? GROUP BY name";
        String startDateTime = getDateStrByFormat(millionsToUtilDate(startTime), "yyyy-MM-dd HH:mm:ss");
        String endDateTime = getDateStrByFormat(millionsToUtilDate(endTime), "yyyy-MM-dd HH:mm:ss");
        return jdbcTemplate.query(sql, new Object[]{startDateTime, endDateTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            };
        });
    }

    @Override
    public List<NameValue> staticByCityCodeforRose(Long startTime, Long endTime) {
        String sql="SELECT chinaencode.domain as name, COUNT(*) as value FROM chinaencode,warnrecord WHERE LEFT(chinaencode.cvalue,4)=LEFT(warnrecord.idnumber,4) AND RIGHT(chinaencode.cvalue,2)='00' AND warnrecord.warntime BETWEEN ? AND ? GROUP BY name";
        String startDateTime = getDateStrByFormat(millionsToUtilDate(startTime), "yyyy-MM-dd HH:mm:ss");
        String endDateTime = getDateStrByFormat(millionsToUtilDate(endTime), "yyyy-MM-dd HH:mm:ss");
        return jdbcTemplate.query(sql, new Object[]{startDateTime, endDateTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            };
        });
    }

    @Override
    public List<NameValue> staticByCountryCodeforRose(Long startTime, Long endTime) {
        String sql="SELECT chinaencode.domain as name, COUNT(*) as value FROM chinaencode,warnrecord WHERE LEFT(chinaencode.cvalue,6)=LEFT(warnrecord.idnumber,6) AND warnrecord.warntime BETWEEN ? AND ? GROUP BY name";
        String startDateTime = getDateStrByFormat(millionsToUtilDate(startTime), "yyyy-MM-dd HH:mm:ss");
        String endDateTime = getDateStrByFormat(millionsToUtilDate(endTime), "yyyy-MM-dd HH:mm:ss");
        return jdbcTemplate.query(sql, new Object[]{startDateTime, endDateTime}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            };
        });
    }

    /**
     * 根据idNum，按月份统计报警信息及其报警次数
     * @param idnum 身份证号
     * */
    @Override
    public List<NameValue> getNVWeightByIdNumUnderMonth(String idnum) {
        String sql="SELECT A.name,count(*) FROM (SELECT CONCAT(YEAR(warnrecord.warntime),\"-\",IF(MONTH(warnrecord.warntime)<10,CONCAT(\"0\",MONTH(warnrecord.warntime)),MONTH(warnrecord.warntime))) as name\n" +
                "FROM warnrecord,userinfo1 \n" +
                "WHERE userinfo1.idnum=? AND userinfo1.idnum=warnrecord.idnumber ) A GROUP BY A.name";
        return jdbcTemplate.query(sql, new Object[]{idnum}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }

    /**
     * 根据idnum，统计object的历史报警时刻
     * @param idnum 身份证号
     * */
    @Override
    public List<NameValue> getWarnTimeListByIdNum(String idnum) {
        String sql="SELECT warnrecord.warntime,1 FROM warnrecord,userinfo1\n" +
                "WHERE userinfo1.idnum=? AND warnrecord.idnumber=userinfo1.idnum";
        return jdbcTemplate.query(sql, new Object[]{idnum}, new RowMapper<NameValue>() {
            @Nullable
            @Override
            public NameValue mapRow(ResultSet resultSet, int i) throws SQLException {
                return new NameValue(
                        resultSet.getString(1),
                        resultSet.getLong(2)
                );
            }
        });
    }
}
